package cn.kgc.javaweb.dao.impl;

import cn.hutool.crypto.SecureUtil;
import cn.kgc.javaweb.dao.UserDao;
import cn.kgc.javaweb.entity.User;
import cn.kgc.javaweb.utils.DataSourceUtil;
import cn.kgc.javaweb.vo.UserVO;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @Author: 课工场
 * @Version: v1.0  2023/5/15
 * @Description:  JDBC
 * 1.导包
 * 2.注册驱动
 * 3.获取连接对象    数据库连接池
 * 4.获取发送sql语句对象
 * 5.执行sql
 * 6.处理结果集
 * 7.释放资源
 */
public class UserDaoImpl implements UserDao {

    @Override
    public User selectByUserName(String username) {
        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "select id,username,password,sex,birth,role_id as roleId from user where username = ?";
        User user = null;
        try {
            user = queryRunner.query(sql, new BeanHandler<>(User.class), username);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return user;
    }

    @Override
    public Integer registerUser(String username, String password) {
        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "insert into user(username,password)values(?,?)";
        try {
            int line = queryRunner.execute(sql, username, password);
            return  line;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public Integer updateUser(User user) {
        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "update user set  sex= ?,birth = ? where username = ?";
        try {
            int line = queryRunner.execute(sql, user.getSex(), user.getBirth(), user.getUsername());
            return line;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public void updatePwd(String username, String newPassword) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "update user set password = ? where username = ?";
        int execute = queryRunner.execute(sql, SecureUtil.md5(newPassword), username);
    }

    @Override
    public List<UserVO> getList(Integer start, Integer limit) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
        /*
        * select user.* ,role.role_name as roleName from user left join role  on user.role_id = role.id  limit ? , ?
        * */

        String sql = "select user.* ,role.role_name as roleName from user left join role  on user.role_id = role.id  limit ? , ?";
        List<UserVO> userVOS = queryRunner.query(sql, new BeanListHandler<>(UserVO.class), start, limit);

        return userVOS;
    }

    @Override
    public Long getTotal() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "select count(username) from user";
        Long query = queryRunner.query(sql, new ScalarHandler<>());
        return query;
    }

    @Override
    public void addUser(User user) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "insert into user (username,password,sex,birth,role_id)values(?,?,?,?,?)";
        queryRunner.execute(sql,user.getUsername(),user.getPassword(),user.getSex(),user.getBirth(),user.getRoleId());
    }

    @Override
    public void updateUserByRoot(User user) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "update user set sex = ? ,birth = ? , role_id = ? where username = ? ";
        queryRunner.execute(sql,user.getSex(),user.getBirth(),user.getRoleId(),user.getUsername());
    }

    @Override
    public void deleteBatch(String[] ids) {

        QueryRunner queryRunner = new QueryRunner();
        Connection connection = DataSourceUtil.getConnection();
        try {
            connection.setAutoCommit(false);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        String sql = "delete from user  where id = ? ";

        for (String id : ids) {
            try {
                queryRunner.execute(connection,sql,Integer.parseInt(id));
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                try {
                    connection.rollback();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                throw  new RuntimeException("删除失败");
            }
        }
        try {
            connection.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public List<User> selectByRoleId(Integer id) {

        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "select * from  user where role_id = ?";
        try {
            List<User> users = queryRunner.query(sql, new BeanListHandler<>(User.class),id);
            return  users;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}
